-- SELECT dbo.fn_getShipping_fgProcessStep_id_mfgPartNumber ( 'MSP-Test-28-C1' )
-- select * from product
 
ALTER  FUNCTION fn_getShipping_fgProcessStep_id_mfgPartNumber( @mfgPartNumber VARCHAR(50) )
RETURNS INT AS 
BEGIN
	DECLARE @fgProcessStepId INT 
	DECLARE @parentProductId INT
	DECLARE @pType VARCHAR(50)

	SET @fgProcessStepId = -1
	SELECT @fgProcessStepId = product.Processstep_id, @pType = P_Type, @parentProductId = P_Parent_Product_ID FROM product, Process_Step WHERE product.ProcessStep_id = Process_Step.ProcessStep_id and Part_Number = @mfgPartNumber and PS_Type_Name = 'Finished Goods'
	
	IF( @pType <> 'CHIP' )
	BEGIN
	 	IF( @parentProductId != -1)
 		BEGIN	
 			SELECT @fgProcessStepId = processStep_ID FROM product WHERE Product_id = @parentProductId
	 	END
 	END
	RETURN @fgProcessStepId
END
